package com.sinosoft.product;

public class DataQuerySql {
	private static String tenSql = "select * from ( select t.productid, t.satelliteid, t.sensorid ,"
			+ " t.topleftlongitude as LEFTTLONG,t.topleftlatitude as LEFTTLAT ,"
			+ " t.toprightlongitude    as RIGHTTLONG,t.toprightlatitude     as RIGHTTLAT,"
			+ " t.bottomrightlongitude as RIGHTBLONG ,t.bottomrightlatitude as RIGHTBLAT,"
			+ " t.bottomleftlongitude     as LEFTBLONG, t.bottomleftlatitude      as LEFTBLAT,"
			+ " t.scenedate as scenedate,t.receivestationid as receive, t.centerlatitude as centerlat,t.centerlongitude as centerlong,t.browsefilelocation  as browsefile, "
			+ " rownum as rn  from zywx_image_";
	private static String allSql = " select t.productid, t.satelliteid, t.sensorid ,"
			+ " t.topleftlongitude as LEFTTLONG,t.topleftlatitude as LEFTTLAT ,"
			+ " t.toprightlongitude    as RIGHTTLONG,t.toprightlatitude     as RIGHTTLAT,"
			+ " t.bottomrightlongitude as RIGHTBLONG ,t.bottomrightlatitude as RIGHTBLAT,"
			+ " t.bottomleftlongitude     as LEFTBLONG, t.bottomleftlatitude      as LEFTBLAT,"
			+ " t.scenedate as scenedate,t.receivestationid as receive, t.centerlatitude as centerlat,t.centerlongitude as centerlong, t.browsefilelocation  as browsefile,"
			+ " rownum as rn  from zywx_image_";
	private static String countSql = " select count(*) as PAGESIZE "
			+ " from zywx_image_";

	// 经纬度
	public static String getTenByLonglat(DataQueryModel datamodel,
			int firstPage, int lastPage) {
		String sql = "";
		sql = tenSql
				+ datamodel.getProductlevel()
				+ " t "
				+ " WHERE sdo_relate(t.geom, "
				+ " SDO_geometry(2003, "
				+ "            4326, "
				+ "           NULL, "
				+ "          SDO_elem_info_array(1, 1003, 3), "
				+ "         SDO_ordinate_array("
				+ datamodel.getLeftTlong()
				+ ", "
				+ "                           "
				+ datamodel.getLeftTlat()
				+ ", "
				+ "                          "
				+ datamodel.getRightBlong()
				+ ", "
				+ "                         "
				+ datamodel.getRightBlat()
				+ ")), "
				+ " 'mask=anyinteract') = 'TRUE'  "
				+ DataQueryModelSet.getSql(datamodel)
				+ DataQueryModelSet.getSateAndSensor(datamodel
						.getSateAndsensor()) + " ) imagetable "
				+ " where imagetable.rn between " + firstPage + " and  "
				+ lastPage;
		return sql;

	}

	public static String getAllByLonglat(DataQueryModel datamodel) {
		String sql = "";
		sql = allSql
				+ datamodel.getProductlevel()
				+ " t "
				+ " WHERE sdo_relate(t.geom, "
				+ " SDO_geometry(2003, "
				+ "            4326, "
				+ "           NULL, "
				+ "          SDO_elem_info_array(1, 1003, 3), "
				+ "         SDO_ordinate_array("
				+ datamodel.getLeftTlong()
				+ ", "
				+ "                           "
				+ datamodel.getLeftTlat()
				+ ", "
				+ "                          "
				+ datamodel.getRightBlong()
				+ ", "
				+ "                         "
				+ datamodel.getRightBlat()
				+ ")), "
				+ " 'mask=anyinteract') = 'TRUE' "
				+ DataQueryModelSet.getSql(datamodel)
				+ DataQueryModelSet.getSateAndSensor(datamodel
						.getSateAndsensor());
		return sql;

	}

	public static String getCountByLonglat(DataQueryModel datamodel) {
		String sql = "";
		sql = countSql
				+ datamodel.getProductlevel()
				+ " t "
				+ " WHERE sdo_relate(t.geom, "
				+ " SDO_geometry(2003, "
				+ "            4326, "
				+ "           NULL, "
				+ "          SDO_elem_info_array(1, 1003, 3), "
				+ "         SDO_ordinate_array("
				+ datamodel.getLeftTlong()
				+ ", "
				+ "                           "
				+ datamodel.getLeftTlat()
				+ ", "
				+ "                          "
				+ datamodel.getRightBlong()
				+ ", "
				+ "                         "
				+ datamodel.getRightBlat()
				+ ")), "
				+ " 'mask=anyinteract') = 'TRUE' "
				+ DataQueryModelSet.getSql(datamodel)
				+ DataQueryModelSet.getSateAndSensor(datamodel
						.getSateAndsensor());
		return sql;

	}

	// 全世界
	public static String getTenByWorld(DataQueryModel datamodel, int firstPage,
			int lastPage) {
		String sql = "";
		sql = tenSql
				+ datamodel.getProductlevel()
				+ " t "
				+ " WHERE sdo_relate(t.geom, "
				+ " SDO_geometry(2003, "
				+ "            4326, "
				+ "           NULL, "
				+ "          SDO_elem_info_array(1, 1003, 3), "
				+ "         SDO_ordinate_array(-180,-90,180,90)), "
				+ " 'mask=anyinteract') = 'TRUE'  "
				+ DataQueryModelSet.getSql(datamodel)
				+ DataQueryModelSet.getSateAndSensor(datamodel
						.getSateAndsensor()) + " ) imagetable "
				+ " where imagetable.rn between " + firstPage + " and  "
				+ lastPage;
		return sql;

	}

	public static String getAllByWorld(DataQueryModel datamodel) {
		String sql = "";
		sql = allSql
				+ datamodel.getProductlevel()
				+ " t "
				+ " WHERE sdo_relate(t.geom, "
				+ " SDO_geometry(2003, "
				+ "            4326, "
				+ "           NULL, "
				+ "          SDO_elem_info_array(1, 1003, 3), "
				+ "         SDO_ordinate_array(-180,-90,180,90)), "
				+ " 'mask=anyinteract') = 'TRUE' "
				+ DataQueryModelSet.getSql(datamodel)
				+ DataQueryModelSet.getSateAndSensor(datamodel
						.getSateAndsensor());
		return sql;

	}

	public static String getCountByWorld(DataQueryModel datamodel) {
		String sql = "";
		sql = countSql
				+ datamodel.getProductlevel()
				+ " t "
				+ " WHERE sdo_relate(t.geom, "
				+ " SDO_geometry(2003, "
				+ "            4326, "
				+ "           NULL, "
				+ "          SDO_elem_info_array(1, 1003, 3), "
				+ "         SDO_ordinate_array(-180,-90,180,90)), "
				+ " 'mask=anyinteract') = 'TRUE' "
				+ DataQueryModelSet.getSql(datamodel)
				+ DataQueryModelSet.getSateAndSensor(datamodel
						.getSateAndsensor());
		return sql;

	}

	// 国家
	public static String getTenByNational(DataQueryModel datamodel,
			int firstPage, int lastPage) {
		String sql = "";
		sql = tenSql
				+ datamodel.getProductlevel()
				+ " t , apsco_geo_world a"
				+ " WHERE a.gid = '"
				+ datamodel.getNationalid()
				+ "'  and sdo_relate(t.geom, "
				+ " SDO_geometry(2003, "
				+ "            4326, "
				+ "           NULL, "
				+ "          SDO_elem_info_array(1, 1003, 1),a.geom.SDO_ORDINATES ),"
				+ " 'mask=anyinteract') = 'TRUE'  "
				+ DataQueryModelSet.getSql(datamodel)
				+ DataQueryModelSet.getSateAndSensor(datamodel
						.getSateAndsensor()) + " ) imagetable "
				+ " where imagetable.rn between " + firstPage + " and  "
				+ lastPage;
		return sql;

	}

	public static String getAllByNational(DataQueryModel datamodel) {
		String sql = "";
		sql = allSql
				+ datamodel.getProductlevel()
				+ " t , apsco_geo_world a"
				+ " WHERE a.gid = '"
				+ datamodel.getNationalid()
				+ "'  and sdo_relate(t.geom, "
				+ " SDO_geometry(2003, "
				+ "            4326, "
				+ "           NULL, "
				+ "          SDO_elem_info_array(1, 1003, 1),  a.geom.SDO_ORDINATES),"

				+ " 'mask=anyinteract') = 'TRUE' "
				+ DataQueryModelSet.getSql(datamodel)
				+ DataQueryModelSet.getSateAndSensor(datamodel
						.getSateAndsensor());
		return sql;

	}

	public static String getCountByNational(DataQueryModel datamodel) {
		String sql = "";
		sql = countSql
				+ datamodel.getProductlevel()
				+ " t , apsco_geo_world a "
				+ " WHERE a.gid = '"
				+ datamodel.getNationalid()
				+ "'  and sdo_relate(t.geom, "
				+ " SDO_geometry(2003, "
				+ "            4326, "
				+ "           NULL, "
				+ "          SDO_elem_info_array(1, 1003, 1),  a.geom.SDO_ORDINATES ),"

				+ " 'mask=anyinteract') = 'TRUE' "
				+ DataQueryModelSet.getSql(datamodel)
				+ DataQueryModelSet.getSateAndSensor(datamodel
						.getSateAndsensor());
		return sql;

	}

	// 省
	public static String getTenByProvince(DataQueryModel datamodel,
			int firstPage, int lastPage) {
		String sql = "";
		sql = tenSql
				+ datamodel.getProductlevel()
				+ " t , apsco_geo_states a"
				+ " WHERE a.gid = '"
				+ datamodel.getProvinceid()
				+ "'  and sdo_relate(t.geom, "
				+ " SDO_geometry(2003, "
				+ "            4326, "
				+ "           NULL, "
				+ "          SDO_elem_info_array(1, 1003, 1),a.geom.SDO_ORDINATES ),"
				+ " 'mask=anyinteract') = 'TRUE'  "
				+ DataQueryModelSet.getSql(datamodel)
				+ DataQueryModelSet.getSateAndSensor(datamodel
						.getSateAndsensor()) + " ) imagetable "
				+ " where imagetable.rn between " + firstPage + " and  "
				+ lastPage;
		return sql;

	}

	public static String getAllByProvince(DataQueryModel datamodel) {
		String sql = "";
		sql = allSql
				+ datamodel.getProductlevel()
				+ " t , apsco_geo_states a"
				+ " WHERE a.gid = '"
				+ datamodel.getProvinceid()
				+ "'  and sdo_relate(t.geom, "
				+ " SDO_geometry(2003, "
				+ "            4326, "
				+ "           NULL, "
				+ "          SDO_elem_info_array(1, 1003, 1),  a.geom.SDO_ORDINATES),"

				+ " 'mask=anyinteract') = 'TRUE' "
				+ DataQueryModelSet.getSql(datamodel)
				+ DataQueryModelSet.getSateAndSensor(datamodel
						.getSateAndsensor());
		return sql;

	}

	public static String getCountByProvince(DataQueryModel datamodel) {
		String sql = "";
		sql = countSql
				+ datamodel.getProductlevel()
				+ " t , apsco_geo_states a "
				+ " WHERE a.gid = '"
				+ datamodel.getProvinceid()
				+ "'  and sdo_relate(t.geom, "
				+ " SDO_geometry(2003, "
				+ "            4326, "
				+ "           NULL, "
				+ "          SDO_elem_info_array(1, 1003, 1),  a.geom.SDO_ORDINATES ),"

				+ " 'mask=anyinteract') = 'TRUE' "
				+ DataQueryModelSet.getSql(datamodel)
				+ DataQueryModelSet.getSateAndSensor(datamodel
						.getSateAndsensor());
		return sql;

	}

	// 县
	public static String getTenByCounty(DataQueryModel datamodel,
			int firstPage, int lastPage) {
		String sql = "";
		sql = tenSql
				+ datamodel.getProductlevel()
				+ " t , apsco_geo_town a"
				+ " WHERE a.gid = '"
				+ datamodel.getCountyid()
				+ "'  and sdo_relate(t.geom, "
				+ " SDO_geometry(2003, "
				+ "            4326, "
				+ "           NULL, "
				+ "          SDO_elem_info_array(1, 1003, 1),a.geom.SDO_ORDINATES ),"
				+ " 'mask=anyinteract') = 'TRUE'  "
				+ DataQueryModelSet.getSql(datamodel)
				+ DataQueryModelSet.getSateAndSensor(datamodel
						.getSateAndsensor()) + " ) imagetable "
				+ " where imagetable.rn between " + firstPage + " and  "
				+ lastPage;
		return sql;

	}

	public static String getAllByCounty(DataQueryModel datamodel) {
		String sql = "";
		sql = allSql
				+ datamodel.getProductlevel()
				+ " t , apsco_geo_town a"
				+ " WHERE a.gid = '"
				+ datamodel.getCountyid()
				+ "'  and sdo_relate(t.geom, "
				+ " SDO_geometry(2003, "
				+ "            4326, "
				+ "           NULL, "
				+ "          SDO_elem_info_array(1, 1003, 1),  a.geom.SDO_ORDINATES),"

				+ " 'mask=anyinteract') = 'TRUE' "
				+ DataQueryModelSet.getSql(datamodel)
				+ DataQueryModelSet.getSateAndSensor(datamodel
						.getSateAndsensor());
		return sql;

	}

	public static String getCountByCounty(DataQueryModel datamodel) {
		String sql = "";
		sql = countSql
				+ datamodel.getProductlevel()
				+ " t , apsco_geo_town a "
				+ " WHERE a.gid = '"
				+ datamodel.getCountyid()
				+ "'  and sdo_relate(t.geom, "
				+ " SDO_geometry(2003, "
				+ "            4326, "
				+ "           NULL, "
				+ "          SDO_elem_info_array(1, 1003, 1),  a.geom.SDO_ORDINATES ),"

				+ " 'mask=anyinteract') = 'TRUE' "
				+ DataQueryModelSet.getSql(datamodel)
				+ DataQueryModelSet.getSateAndSensor(datamodel
						.getSateAndsensor());
		return sql;

	}

}
